﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using MODEL;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    public class StudentDAL
    {
        public void Dr2Student(DataRow row,StudentModel model)
        {
            model.SID = (int)row["SID"];
            model.SCID = (int)row["SCID"];
            model.SLoginName = (string)row["SLoginName"];
            model.SCnName = (string)row["SCnName"];
            model.SPwd = (string)row["SPwd"];
            model.STel = (string)row["STel"];
            model.SGender = (bool)row["SGender"];
            model.SIsDel = (bool)row["SIsDel"];
            model.SAddtime = (DateTime)row["SAddtime"];
            model.SClass = new ClassModel();
            if (row["SSupport"].ToString() != "")
            {
                model.SSupport = (int)row["SSupport"];
            }
            if (row["SDisSupport"].ToString() != null)
            {
                model.SDisSupport = (int)row["SDisSupport"];
            }
            new ClassesDAL().Dr2Class(row, model.SClass);
        }

        SqlHelper sqlhelper = new SqlHelper();

        public StudentModel QueryByCondition(string name, string pwd)
        {
            DataTable tabel = sqlhelper.ExecuteDataTable("select * from Classes c,Students s where c.CID=s.SCID and s.SLoginName=@name and s.SPwd=@pwd",
                new SqlParameter("@name", name),
                new SqlParameter("@pwd", pwd));
            if (tabel.Rows.Count < 1)
            {
                return null;
            }
            else if (tabel.Rows.Count > 1)
            {
                throw new Exception("Error");
            }
            else
            {
                DataRow row = tabel.Rows[0];
                StudentModel model = new StudentModel();
                Dr2Student(row, model);
                return model;
            }
        }

        public StudentModel QueryByCondition(string id)
        {
            DataTable table = sqlhelper.ExecuteDataTable("select * from Classes c,Students s where c.CID=s.SCID and s.SID=@id and s.SIsDel=0",
                new SqlParameter("@id", id));
            if (table.Rows.Count > 1)
            {
                throw new Exception("Error");
            }
            else if (table.Rows.Count < 1)
            {
                return null;
            }
            else
            {
                DataRow row = table.Rows[0];
                StudentModel model = new StudentModel();
                Dr2Student(row, model);
                return model;
            }
        }

        public List<StudentModel> QueryByPageIndex(int pageIndex)
        {
            string pageSize = "3";
            string strSql = @"select top " + pageSize + " * from Classes c,Students s where c.CID=s.SCID and s.SIsDel=0 and s.SID not in (select top " + (pageIndex - 1) * int.Parse(pageSize) + " SID FROM Students where SIsDel=0) order by SID ";
            DataTable table = sqlhelper.ExecuteDataTable(strSql);
            if (table != null)
            {
                List<StudentModel> list = new List<StudentModel>();
                foreach (DataRow row in table.Rows)
                {
                    StudentModel model = new StudentModel();
                    Dr2Student(row, model);
                    list.Add(model);
                }
                return list;
            }
            else
            {
                return null;
            }
        }


        public int AddNew(StudentModel model)
        {
            string strSql = @"insert into Students(SCID,SLoginName,SCnName,SPwd,SGender,STel,SIsDel,SAddtime) values
                                            (@SCID,@SLoginName,@SCnName,@SPwd,@SGender,@STel,@SIsDel,@SAddtime)";
            List<SqlParameter> paras = new List<SqlParameter>();
            paras.Add(new SqlParameter("@SCID",model.SCID));
            paras.Add(new SqlParameter("@SLoginName", model.SLoginName));
            paras.Add(new SqlParameter("@SCnName", model.SCnName));
            paras.Add(new SqlParameter("@SPwd", model.SPwd));
            paras.Add(new SqlParameter("@SGender", model.SGender));
            paras.Add(new SqlParameter("@STel", model.STel));
            paras.Add(new SqlParameter("@SIsDel", model.SIsDel));
            paras.Add(new SqlParameter("@SAddtime", model.SAddtime));
            return sqlhelper.ExecuteNonQuery(strSql, paras.ToArray());
        }

        public List<StudentModel> ListAll()
        {
            DataTable table = sqlhelper.ExecuteDataTable("select * from Classes c,Students s where c.CID=s.SCID and s.SIsDel=0");
            if (table.Rows.Count <= 0)
            {
                return null;
            }
            else
            {
                List<StudentModel> list = new List<StudentModel>();
                foreach (DataRow row in table.Rows)
                {
                    StudentModel model = new StudentModel();
                    Dr2Student(row, model);
                    list.Add(model);
                }
                return list;
            }
        }


        public int SoftDel(string id)
        {
            return sqlhelper.ExecuteNonQuery("update Students set SIsDel=1 where SID=@id",
                new SqlParameter("@id", id));
        }

        public int Update(StudentModel model)
        {
            string strSql = "update Students set SPwd=@SPwd,SCnName=@SCnName,SCID=@SCID,SGender=@SGender,STel=@STel where SID=@SID";
            List<SqlParameter> paras = new List<SqlParameter>();
            paras.Add(new SqlParameter("@SPwd", model.SPwd));
            paras.Add(new SqlParameter("@SCnName", model.SCnName));
            paras.Add(new SqlParameter("@SCID", model.SCID));
            paras.Add(new SqlParameter("@SGender", model.SGender));
            paras.Add(new SqlParameter("@STel", model.STel));
            paras.Add(new SqlParameter("@SID", model.SID));
            return sqlhelper.ExecuteNonQuery(strSql, paras.ToArray());
        }

        public object UpdateSSupport_Output(StudentModel model)
        {
            string strSql = "update Students set SSupport=@SSupport where SID=@SID;select SSupport from Students where SID=@SID";
            List<SqlParameter> paras = new List<SqlParameter>();
            paras.Add(new SqlParameter("@SSupport", model.SSupport));
            paras.Add(new SqlParameter("@SID", model.SID));
            return sqlhelper.ExecuteScalar(strSql, paras.ToArray());
        }

        public object UpdateSDisSupport_Output(StudentModel model)
        {
            string strSql = "update Students set SDisSupport=@SDisSupport where SID=@SID;select SDisSupport from Students where SID=@SID";
            List<SqlParameter> paras = new List<SqlParameter>();
            paras.Add(new SqlParameter("@SDisSupport", model.SDisSupport));
            paras.Add(new SqlParameter("@SID", model.SID));
            return sqlhelper.ExecuteScalar(strSql, paras.ToArray());
        }
    }
}
 